﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Kugar.Core.ExtMethod;
using Kugar.Core.BaseStruct;
using Kugar.Core.Log;
using Kugar.Core.Log.Interface;


namespace Kugar.Core.Data.DBCommon
{
    public abstract class DbDatabaseHelperBase :MarshalByRefObject, IKugarConnection
    {
        protected DbTransaction trans = null;

        private DbDatabaseHelperBase()
        {
            ExceptionHandleType = ExceptionHandleType.LogException;
        }

        public DbDatabaseHelperBase(string connString)
            : this()
        {
            ConnectionString = connString;
            OpenConnection();
            this.ExceptionHandleType = ExceptionHandleType.ThrowException;
        }

        public DbDatabaseHelperBase(DbConnection conn)
            : this()
        {
            Connection = conn;
            OpenConnection();
            this.ExceptionHandleType = ExceptionHandleType.ThrowException;
        }

        /// <summary>
        ///     对于错误的处理，如果需要抛出错误，则返回true，否则，返回false
        /// </summary>
        /// <param name="ex"></param>
        /// <returns></returns>
        protected bool HandleException(Exception ex)
        {
            if (ExceptionHandleType.IsContans(ExceptionHandleType.LogException))
            {
                ILogger logger = LoggerManager.CoreLogger;

                if (logger!=null)
	            {
                    logger.Error(ex.Message);
	            }

                return true;
            }

            if (ExceptionHandleType.IsContans(ExceptionHandleType.ThrowException))
            {
                //throw ex;
                return true;
            }

            return false;
        }

        protected abstract void OpenConnection();

        public virtual void DisposeObject()
        {
            if (trans != null)
            {
                try
                {
                    trans.Rollback();
                }
                catch (Exception)
                {
                    
                }
                
            }

            this.Connection.Close();
        }

        public virtual void Dispose()
        {
            var pool = (RecyclablePool<IKugarConnection>)Pool;

            if (Pool != null)
            {
                pool.RecycleObject(this);
            }
            else
            {
                DisposeObject();
            }
        }

        public virtual IRecyclablePool<IRecyclable> Pool { get; set; }

        public virtual DbConnection Connection { get; protected set; }
        public virtual string ConnectionString { get; protected set; }

        public virtual ExceptionHandleType ExceptionHandleType { get; set; }

        public virtual DbCommand CreateCommand()
        {
            if (Connection != null)
            {
                return Connection.CreateCommand();
            }
            else
            {
                return null;
            }
        }

        public virtual DbTransaction BeginTransaction()
        {
            var t = Connection.BeginTransaction();

            trans = t;

            return t;
        }

        public virtual void RollbackTransaction()
        {
            if (trans != null)
            {
                trans.Rollback();
            }
            trans = null;
        }

        public virtual void CommitTransaction()
        {
            if (trans != null)
            {
                trans.Commit();
            }

            trans = null;
        }

        public virtual DbTransaction CurrentTransaction { get { return trans; }  }

        #region "ExecuteNoQuery"

        public int ExecuteNoQuery(string sqlStr)
        {
            return ExecuteNoQuery(sqlStr, null,null);
        }

        public int ExecuteNoQuery(string sqlStr, params object[] param)
        {
            return ExecuteNoQuery(sqlStr, null, param);
        }

        public int ExecuteNoQuery(string sqlStr, bool? isThrowError, params object[] param)
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                s = string.Format(sqlStr, param);
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteNoQuery(cmd, isThrowError);
        }

        public int ExecuteNoQuery(DbCommand cmd)
        {
            return ExecuteNoQuery(cmd, null);
        }

        public int ExecuteNoQuery(DbCommand cmd, bool? isThrowError)
        {
            cmd.Connection = this.Connection;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                CheckCmdParmIsNull(cmd.Parameters);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex) && !isThrowError.HasValue ))
                {
                    throw;
                }
                else
                {
                    return 0;
                }

                //HandleException(ex);
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();
            }

            
        }


        #endregion

        #region "ExecuteReader"

        public virtual DbDataReader ExecuteReader(string sqlStr)
        {
            return ExecuteReader(sqlStr, null,null);
        }

        public virtual DbDataReader ExecuteReader(string sqlStr, params object[] param)
        {
            return ExecuteReader(sqlStr, null, param);
        }

        public virtual DbDataReader ExecuteReader(string sqlStr, bool? isThrowError, params object[] param)
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                if (param == null || param.Length <= 0)
                {
                    s = sqlStr;
                }
                else
                {
                    s = string.Format(sqlStr, param);
                }
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteReader(cmd, isThrowError);
        }

        public virtual DbDataReader ExecuteReader(DbCommand cmd)
        {
            return ExecuteReader(cmd, null);
        }

        public virtual DbDataReader ExecuteReader(DbCommand cmd, bool? isThrowError)
        {
            cmd.Connection = this.Connection;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                CheckCmdParmIsNull(cmd.Parameters);
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex) && !isThrowError.HasValue  ))
                {
                    throw;
                }
            }
            finally
            {
                cmd.Connection = null;
                cmd.Transaction = null;
                cmd.Dispose();
            }

            return null;
        }

        #endregion

        #region "ExecuteScalar"

        public virtual object ExecuteScalar(string sqlStr)
        {
            return ExecuteScalar(sqlStr, null,null);
        }

        public virtual object ExecuteScalar(string sqlStr,params object[] param)
        {
            return ExecuteScalar(sqlStr, null,param);
        }

        public virtual object ExecuteScalar(string sqlStr, bool? isThrowError, params object[] param)
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                s = string.Format(sqlStr, param);
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteScalar(cmd,isThrowError);
        }

        public virtual object ExecuteScalar(DbCommand cmd)
        {
            return ExecuteScalar(cmd, null);
        }

        public virtual object ExecuteScalar(DbCommand cmd, bool? isThrowError)
        {
            cmd.Connection = this.Connection;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                CheckCmdParmIsNull(cmd.Parameters);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex) && !isThrowError.HasValue  ))
                {
                    throw;
                }
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();
            }

            return null;
        }

        #endregion

        #region "ExecuteDataTable"

        public virtual DataTable ExecuteDataTable(string sqlStr)
        {
            return ExecuteDataTable(sqlStr, null,null);
        }

        public virtual DataTable ExecuteDataTable(string sqlStr, params object[] param)
        {
            return ExecuteDataTable(sqlStr, null, null);
        }

        public virtual DataTable ExecuteDataTable(string sqlStr, bool? isThrowError, params object[] param)
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                s = string.Format(sqlStr, param);
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteDataTable(cmd, isThrowError);
        }

        public virtual DataTable ExecuteDataTable(DbCommand cmd)
        {
            var tbl = new DataTable();

            ExecuteDataTable(cmd, tbl,null);

            return tbl;
        }

        public virtual DataTable ExecuteDataTable(DbCommand cmd, bool? isThrowError)
        {
            var tbl = new DataTable();

            ExecuteDataTable(cmd, tbl, isThrowError);

            return tbl;
        }

        public virtual void ExecuteDataTable(DbCommand cmd, DataTable tbl)
        {
            ExecuteDataTable(cmd, tbl, null);
        }

        public virtual void ExecuteDataTable(DbCommand cmd, DataTable tbl, bool? isThrowError)
        {
            try
            {
                CheckCmdParmIsNull(cmd.Parameters);
                ExecuteDataTableInternal(cmd, tbl);
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (!isThrowError.HasValue && HandleException(ex)))
                {
                    throw;
                }
            }
        }

        protected abstract void ExecuteDataTableInternal(DbCommand cmd, DataTable tbl);

        #endregion


        #region "ExecuteDataSet"

        public virtual DataSet ExecuteDataSet(string sqlStr)
        {
            return ExecuteDataSet(sqlStr, null, null);
        }

        public virtual DataSet ExecuteDataSet(string sqlStr, params object[] param)
        {
            return ExecuteDataSet(sqlStr, null, param);
        }

        public virtual DataSet ExecuteDataSet(string sqlStr, bool? isThrowError, params object[] param)
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                s = string.Format(sqlStr, param);
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteDataSet(cmd, isThrowError);
        }

        public virtual DataSet ExecuteDataSet(DbCommand cmd)
        {
            var ds = new DataSet();

            ExecuteDataSet(cmd, ds, null);

            return ds;
        }

        public virtual DataSet ExecuteDataSet(DbCommand cmd, bool? isThrowError)
        {
            var ds = new DataSet();

            ExecuteDataSet(cmd, ds, isThrowError);

            return ds;
        }

        public virtual void ExecuteDataSet(DbCommand cmd, DataSet ds)
        {
            ExecuteDataSet(cmd, ds, null);
        }

        public virtual void ExecuteDataSet(DbCommand cmd, DataSet ds, bool? isThrowError)
        {
            try
            {
                CheckCmdParmIsNull(cmd.Parameters);
                ExecuteDataSetInternal(cmd, ds);
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex)&&!isThrowError.HasValue  ))
                {
                    throw;
                }
            }
        }


        protected abstract void ExecuteDataSetInternal(DbCommand cmd, DataSet ds);

        #endregion

        public abstract DataTable GetDatabaseTableSchema(string srcTableName, SchemaType schemaType);
        public abstract DataSet GetDatabaseAllTableSchema(SchemaType schemaType);
        public abstract string[] GetAllTableNames();



        #region "ExecuteSingleRow"

        public Dictionary<string, object> ExecuteSingleRow(string sqlStr)
        {
            return ExecuteSingleRow(sqlStr, null,null);
        }

        public Dictionary<string, object> ExecuteSingleRow(string sqlStr, params object[] param)
        {
            return ExecuteSingleRow(sqlStr, null, param);
        }

        public Dictionary<string, object> ExecuteSingleRow(string sqlStr, bool? isThrowError, params object[] param)
        {
            using (var dr = ExecuteReader(sqlStr, isThrowError, param))
            {
                if (dr.HasRows && dr.Read())
                {
                    var t = new Dictionary<string, object>(dr.FieldCount, StringComparer.CurrentCultureIgnoreCase);

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        t.Add(dr.GetName(i), dr[i]);
                    }

                    return t;
                }
                else
                {
                    return null;
                }
            }
        }

        public Dictionary<string, object> ExecuteSingleRow(DbCommand cmd)
        {
            return ExecuteSingleRow(cmd, null);
        }

        public Dictionary<string, object> ExecuteSingleRow(DbCommand cmd, bool? isThrowError)
        {
            using (var dr = ExecuteReader(cmd, isThrowError))
            {
                if (dr.HasRows && dr.Read())
                {
                    var t = new Dictionary<string, object>(dr.FieldCount, StringComparer.CurrentCultureIgnoreCase);

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        t.Add(dr.GetName(i), dr[i]);
                    }

                    return t;
                }
                else
                {
                    return null;
                }
            }
        }

        #endregion



        #region "ExecuteScalar<T>"

        public T ExecuteScalar<T>(string sqlStr)
        {
            return ExecuteScalar<T>(sqlStr, null, null);
        }

        public T ExecuteScalar<T>(string sqlStr, params object[] param)
        {
            return ExecuteScalar<T>(sqlStr, null, param);
        }

        public T ExecuteScalar<T>(string sqlStr,bool? isThrowError, params object[] param)
        {
            var ret = ExecuteScalar(sqlStr,isThrowError, param);

            try
            {
                return (T)Convert.ChangeType(ret, typeof(T));
            }
            catch (Exception)
            {
                return default(T);
            }
        }

        public T ExecuteScalar<T>(DbCommand cmd)
        {
            return ExecuteScalar<T>(cmd, null);
        }

        public T ExecuteScalar<T>(DbCommand cmd,bool? isThrowError)
        {
            var ret = ExecuteScalar(cmd,isThrowError);

            try
            {
                return (T)Convert.ChangeType(ret, typeof(T));
            }
            catch (Exception)
            {
                return default(T);
            }
        }

        #endregion


        #region "ExecuteDataEntity<T>"

        public virtual IEnumerable<T> ExecuteDataEntity<T>(DbCommand cmd, Action<T, DbDataReader> afterPreRowBuild = null, Action<IEnumerable<T>, DbDataReader> afterAllRowBuild = null,bool? isThrowError=null) where T : class ,new()
        {
            cmd.Connection = this.Connection;

            try
            {
                using (var dr = this.ExecuteReader(cmd, isThrowError))
                {
                    if (dr.HasRows)
                    {
                        var tlist = dr.DataRowToObjectListNoAttribute<T>(afterPreRowBuild, afterAllRowBuild);

                        return tlist;
                    }
                    else
                    {
                        return null;
                    }                    
                }

            }
            catch (Exception)
            {
                
                throw;
            }




        }

        public virtual IEnumerable<T> ExecuteDataEntity<T>(string sqlStr, Action<T, DbDataReader> afterPreRowBuild = null, Action<IEnumerable<T>, DbDataReader> afterAllRowBuild = null,bool? isThrowError=null, params object[] param) where T : class ,new()
        {
            var cmd = this.CreateCommand();

            string s = null;

            if (param != null)
            {
                s = string.Format(sqlStr, param);
            }
            else
            {
                s = sqlStr;
            }
            cmd.CommandText = s;

            return ExecuteDataEntity<T>(cmd, afterPreRowBuild, afterAllRowBuild,isThrowError);
        }

        public virtual IEnumerable<T> ExecuteDataEntity<T>(string sqlStr, Action<T, DbDataReader> afterPreRowBuild = null, Action<IEnumerable<T>, DbDataReader> afterAllRowBuild = null) where T : class ,new()
        {
            return ExecuteDataEntity<T>(sqlStr, afterPreRowBuild, afterAllRowBuild, null);
        }

        #endregion

        #region "ExecutePagingDataTable"

        public virtual VM_PagedDataTable ExecutePagingDataTable(PagingArgs args,bool? isThrowError=null)
        {
            var count = this.ExecuteScalar(BuildGetRecordCountSql(args),isThrowError);

            if (count is DBNull || count == null || count.ToInt() <= 0)
            {
                args.RecordCount = 0;

                return new VM_PagedDataTable(args, null);
            }

            args.RecordCount = count.ToInt();

            try
            {
                args.EnsurePageIndex();

                var tbl = ExecuteDataTable(BuildPagingSql(args),isThrowError);

                return new VM_PagedDataTable(args, tbl);
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex)&&!isThrowError.HasValue  ))
                {
                    throw;
                }
                else
                {
                    return null;
                }
            }


        }

        public virtual VM_PagedDataReader ExecutePagingDataReader(PagingArgs args,bool? isThrowError=null)
        {
            var count = this.ExecuteScalar(BuildGetRecordCountSql(args),isThrowError);

            if (count is DBNull || count == null || ((int)count) <= 0)
            {
                args.RecordCount = 0;

                return new VM_PagedDataReader(args, null);
            }

            args.RecordCount = (int)count;

            try
            {
                args.EnsurePageIndex();

                var tbl = ExecuteReader(BuildPagingSql(args));

                return new VM_PagedDataReader(args, tbl);
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex)&&!isThrowError.HasValue  ))
                {
                    throw;
                }
                else
                {
                    return null;
                }
            }


        }

        public virtual VM_PagedObjectList<T> ExecutePagingDataEntity<T>(PagingArgs args, Action<T, DbDataReader> afterPreRowBuild = null, Action<IEnumerable<T>, DbDataReader> afterAllRowBuild = null,bool? isThrowError=null) where T : class, new()
        {
            var count = this.ExecuteScalar(BuildGetRecordCountSql(args),isThrowError);

            if (count is DBNull || count.ToInt32() <= 0)
            {
                args.RecordCount = 0;

                return new VM_PagedObjectList<T>(args, null);
            }

            args.RecordCount = count.ToInt32();


            try
            {
                args.EnsurePageIndex();

                var tbl = ExecuteDataEntity<T>(BuildPagingSql(args), afterPreRowBuild, afterAllRowBuild,isThrowError);

                return new VM_PagedObjectList<T>(args, tbl);
            }
            catch (Exception ex)
            {
                if (isThrowError == true | (HandleException(ex)&&!isThrowError.HasValue  ))
                {
                    throw;
                }
                else
                {
                    return null;
                }
            }

        }

        #endregion

        /// <summary>
        ///     构建分页语句
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        protected abstract string BuildPagingSql(PagingArgs args);

        /// <summary>
        ///     构建读取所有记录数的sql语句,比如只能返回一个数值
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        protected abstract string BuildGetRecordCountSql(PagingArgs args);

        protected void CheckCmdParmIsNull(DbParameterCollection collection)
        {
            if (collection!=null && collection.Count>0)
            {
                foreach (DbParameter par in collection)
                {
                    if (par.Value == null && par.IsNullable)
                    {
                        par.Value = DBNull.Value;
                    }
                }
            }

        }

        //public abstract VM_PagedDataTable ExecutePagingDataTable(PagingArgs args);
        //public abstract VM_PagedDataReader ExecutePagingDataReader(PagingArgs args);
        //public abstract VM_PagedObjectList<T> ExecutePagingDataEntity<T>(PagingArgs args, Action<T, DbDataReader> afterPreRowBuild, Action<IEnumerable<T>, DbDataReader> afterAllRowBuild) where T : class, new();
    }
}
